package com.leexuan.ordersystem.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.leexuan.ordersystem.beans.Money;

public class MoneyDB extends DB {

	private String ADD_MONEY = "INSERT INTO money VALUES (?,?,?)";

	private String DEL_MONEY = "DELETE FROM money WHERE mid=?";

	private String GET_MONEYS = "SELECT m.mid,m.account,m.iid,CONVERT(VARCHAR(30),m.date,111) as date FROM money AS m  WHERE m.iid=? ORDER BY m.mid";

	public boolean addMoneyr(Money st) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(ADD_MONEY);

			pstmt.setString(1, st.getDate());
			pstmt.setFloat(2, st.getAccount());
			pstmt.setInt(3, st.getIid());

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean delDeliver(int mid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(DEL_MONEY);

			pstmt.setInt(1, mid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public ArrayList getMoneys(int iid) throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList moneys = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_MONEYS, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			pstmt.setInt(1, iid);

			rs = pstmt.executeQuery();

			while (rs.next()) {

				Money money = new Money();

				money.setDate(rs.getString("date"));
				money.setIid(rs.getInt("iid"));
				money.setAccount(rs.getFloat("account"));

				moneys.add(money);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return moneys;
	}
}
